package com.heroh.db;

import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 包名称：com.yss
 * 类名称：CreateSqlGenerator
 * 类描述：建表语句备份工具类
 * 创建人：@author jianshengxiong
 * 创建时间：2019/7/20/10:50
 */
@Slf4j
public class SqlGenerator {
    /**
     * 生成文件地址
     */
    private String destFile;
    /**
     * 通过前缀过滤表名,null表示不过滤
     */
    private String preFilter;
    /**
     * 数据库schema
     */
    private String schema;
    /**
     * 数据库配置
     */
    private ConnectionBean connectionBean;

    public SqlGenerator(Builder builder) {
        this.destFile = builder.destFile;
        this.preFilter = builder.preFilter;
        this.connectionBean = builder.connectionBean;
        this.schema = builder.schema;
    }

    /**
     * 获取数据库下的所有表名
     */
    private List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        Connection conn = connectionBean.getConnection();
        ResultSet rs = null;
        try {
            // 获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            // 从元数据中获取到所有的表名
            rs = db.getTables(schema, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                // 返回1-catalog，2-schema，3-表名
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("getTableNames failure");
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
                System.err.println("close ResultSet failure");
            }
        }
        return tableNames;
    }


    /**
     * <p>
     * 生成建表语句
     * </p>
     *
     * @param tableName
     * @return String
     * @author 叶新东（18126064335） 2018年9月6日 上午9:35:49
     */
    private String generateCreateTableSql(String tableName) {
        String sql = String.format("SHOW CREATE TABLE %s", tableName);
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = connectionBean.getConnection();
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                // 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
                return rs.getString(2);
            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (null != pstmt) {
                    pstmt.close();
                }

            } catch (Exception e2) {
                e.printStackTrace();
                System.err.println("关闭流异常");
            }
        }
        return null;
    }


    /**
     * <p>
     * 备份表数据
     * </p>
     *
     * @param tableName
     * @return
     * @author （） 2018年9月6日 上午10:18:07
     */
    private String bakTableData(String tableName) {
        try {
            // 备份建表语句
            String createTableSql = generateCreateTableSql(tableName);
           // String sql = "drop table if EXISTS "+tableName+";\n" + createTableSql + ";\n\n";
            String sql = createTableSql.replace("TABLE", "TABLE IF NOT EXISTS") + ";\n\n";
            return sql;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * <开始备份>
     *
     * @author 2018年9月6日 下午3:30:43
     */
    public void startBak() {
        log.info("开始备份建表语句:");
        try {
            List<String> tableNames = getTableNames();
            log.info("数据库表:" + tableNames);
            final List<String> vTableNames = new ArrayList<>();
            if (preFilter == null) {
                vTableNames.addAll(tableNames);
            } else {
                tableNames.forEach(name -> {
                    if (name.startsWith(preFilter)) {
                        vTableNames.add(name);
                    }
                });
            }
            log.info("备份数据库表:" + vTableNames);
            FileWriter fileWriter = new FileWriter(destFile);
            for (String tableName : vTableNames) {
                fileWriter.write(bakTableData(tableName));
            }
            // 关闭
            fileWriter.close();
            // 统一关闭连接
            connectionBean.closeConnection();
            log.info("备份建表语句完成"+destFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static class Builder {
        private String destFile;
        private String preFilter;
        private ConnectionBean connectionBean;
        private String schema;

        public SqlGenerator build() {
            if(StringUtils.isEmpty(destFile)) {
                throw new RuntimeException("目标文件不能为空");
            }
            if(StringUtils.isEmpty(connectionBean)) {
                throw new RuntimeException("数据库连接配置不能为空");
            }
            return new SqlGenerator(this);
        }

        public Builder setDestFile(String destFile) {
            this.destFile = destFile;
            return this;
        }

        public Builder setPreFilter(String preFilter) {
            this.preFilter = preFilter;
            return this;
        }

        public Builder setConnectionBean(ConnectionBean connectionBean) {
            this.connectionBean = connectionBean;
            return this;
        }

        public Builder setSchema(String schema) {
            this.schema = schema;
            return this;
        }
    }
}